#！/bin/bash

source /etc/profile

if [ -n "$1" ]
then
        do_date=$1
else
        do_date=`date -d "-1 day" +%F`
fi
sql="
set hive.exec.mode.local.auto=false;
drop table if exists tmp.dim_trade_shops_org_lalian;
create table tmp.dim_trade_shops_org_lalian as
(
select
t1.shopid,
t1.shopname,
t2.id as cityId,
t2.orgname as cityName,
t3.id as regionId,
t3.orgname as regionName,
'$do_date' as start_dt,
'9999-12-31' as end_dt
from
(
        select shopId, shopName, areaId
        from ods.ods_trade_shops
        where dt='$do_date') t1
left join
(
        select id, parentId, orgname, orglevel
        from ods.ods_trade_shop_admin_org
        where orglevel=2 and dt='$do_date') t2
on t1.areaid = t2.id
left join
(
        select id, parentId, orgname, orglevel
        from ods.ods_trade_shop_admin_org
        where orglevel=1 and dt='$do_date') t3
on t2.parentid = t3.id
union all 
select
    dim.shopid,
    dim.shopname,
    dim.cityId,
    dim.cityName,
    dim.regionId,
    dim.regionName,
    dim.start_dt,
    case when dim.end_dt >= '9999-12-31' and tmp.shopid is not null
        then '$do_date' --关联上的是到期了，要把end_dt改成今天
        else dim.end_dt
    end as end_dt
from  dim.dim_trade_shops_org_lalian dim
left join
(
    select
    t1.shopid,
    t1.shopname,
    t2.id as cityid,
    t2.orgname as cityName,
    t3.id as region_id,
    t3.orgname as region_name
    from
    (
            select shopId, shopName, areaId
            from ods.ods_trade_shops
            where dt='$do_date') t1
    left join
    (
            select id, parentId, orgname, orglevel
            from ods.ods_trade_shop_admin_org
            where orglevel=2 and dt='$do_date') t2
    on t1.areaid = t2.id
    left join
    (
            select id, parentId, orgname, orglevel
            from ods.ods_trade_shop_admin_org
            where orglevel=1 and dt='$do_date') t3
    on t2.parentid = t3.id
) tmp
on dim.shopid = tmp.shopid
);

insert overwrite table dim.dim_trade_shops_org_lalian
select * from tmp.dim_trade_shops_org_lalian;
"

hive -e "$sql"

